<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" 
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
	xmlns:gml="http://www.opengis.net/gml" 
	xmlns:ogr="http://ogr.maptools.org/"
	xmlns:geo="http://www.w3.org/2003/01/geo/wgs84_pos#"
	xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
	xmlns:nuts="http://nuts.geovocab.org/id/"
	xmlns:ngeo="http://geovocab.org/geometry#"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	xmlns:owl="http://www.w3.org/2002/07/owl#">
	
<xsl:output method="text" indent="yes" />
<xsl:template match="/ogr:FeatureCollection">

	<xsl:result-document href="spatial_index.sql">
	
		DROP TABLE regions;
		CREATE TABLE regions (id INT NOT NULL AUTO_INCREMENT, code VARCHAR(6), uri VARCHAR(50), level SMALLINT, shape GEOMETRY, bbox MBR);
		
		INSERT INTO regions (code, uri, level, shape) VALUES
		<xsl:for-each select='gml:featureMember/ogr:NUTS_RG_60M_2006' >
			 ('<xsl:value-of select="ogr:NUTS_ID" />','http://nuts.geovocab.org/id/<xsl:value-of select="ogr:NUTS_ID" />',<xsl:value-of select="ogr:STAT_LEVL_" />,'
			<xsl:apply-templates select="ogr:geometryProperty/child::node()"/>
			')
			<xsl:if test="position() != last()">,</xsl:if>
		</xsl:for-each>
		;
		
		UPDATE regions SET bbox = mbr(shape);
	</xsl:result-document>

</xsl:template>

<xsl:template match="gml:MultiPolygon|gml:MultiSurface">
	MULTIPOLYGON (
	<xsl:for-each select='child::gml:polygonMember|child::gml:surfaceMember' >
		(
		<xsl:for-each select='descendant::gml:LinearRing' >
			<xsl:apply-templates select="descendant::gml:posList|descendant::gml:coordinates"/>
			<xsl:if test="position() != last()">,</xsl:if>
		</xsl:for-each>
		)
		<xsl:if test="position() != last()">,</xsl:if>
	</xsl:for-each>
	)
</xsl:template>

<xsl:template name="polygon" match="gml:Polygon|gml:Surface">
	POLYGON (
	<xsl:for-each select='descendant::gml:LinearRing' >
		<xsl:apply-templates select="descendant::gml:posList|descendant::gml:coordinates"/>
		<xsl:if test="position() != last()">,</xsl:if>
	</xsl:for-each>
	)
</xsl:template>

<xsl:template match="gml:LineString|gml:Curve">
	LINESTRING (
	<xsl:apply-templates select="descendant::gml:posList|descendant::gml:coordinates"/>
	)
</xsl:template>

<xsl:template match="gml:posList|gml:coordinates">
	(
	<xsl:call-template name="format-posList">
		<xsl:with-param name="text" select="self::node()"/>
	</xsl:call-template>
	)
</xsl:template>

<xsl:template name="format-posList">
  <xsl:param name="text"/>
  <xsl:choose>
    <xsl:when test="not(contains($text, ' '))">
		<xsl:value-of select="substring-before($text, ',')"/><xsl:text> </xsl:text><xsl:value-of select="substring-after($text, ',')"/>
    </xsl:when>
    <xsl:otherwise>
		<xsl:value-of select="substring-before($text, ',')"/><xsl:text> </xsl:text><xsl:value-of select="substring-before(substring-after($text, ','), ' ')"/>,
		<xsl:call-template name="format-posList">
			<xsl:with-param name="text" select="substring-after($text, ' ')"/>
		</xsl:call-template>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

</xsl:stylesheet>